﻿//引入Excel的COM组件 

using System;
using System.Data;
using System.Drawing;
using System.IO;
using System.Reflection;
using System.Windows.Forms;
using BondedWareHouse.Properties;
using Microsoft.Office.Interop.Excel;
using Application = Microsoft.Office.Interop.Excel.Application;
using DataTable = System.Data.DataTable;
using Font = System.Drawing.Font;

namespace ExcelPrinter
{
    /// <summary>
    /// Excel单元格范围内的边框及内部网格线
    /// </summary>
    public enum BordersEdge { xlLineStyleNone, xlLeft, xlRight, xlTop, xlBottom, xlDiagonalDown, xlDiagonalUp, xlInsideHorizontal, xlInsideVertical }

    /// <summary>
    /// Excel线样
    /// </summary>
    public enum BordersLineStyle { xlContinuous, xlDash, xlDashDot, xlDashDotDot, xlDot, xlDouble, xlLineStyleNone, xlSlantDashDot }

    public enum RangHAlignment { xlHAlignCenter, xlHAlignCenterAcrossSelection, xlHAlignDistributed, xlHAlignFill, xlHAlignGeneral, xlHAlignJustify, xlHAlignLeft, xlHAlignRight }
    public enum RangVAlignment { xlVAlignBottom, xlVAlignCenter, xlVAlignDistributed, xlVAlignJustify, xlVAlignTop }


    /// <summary>
    /// Excel单元格范围内的边框及内部网格线粗细
    /// </summary>
    public enum BordersWeight { xlHairline, xlMedium, xlThick, xlThin }

    /// <summary>
    /// 该类主要定义Excel的程序对象，启动Excel并打印及保存。可能依赖于Interop.VBIDE及Interop.Microsoft.Office.Core，如果需要，请加上。
    /// </summary>
    public class ExcelBase
    {
        private Application _xlApp;							//Excel应用程序
        private Workbook _xlWorkbook;							//Excel工作薄，默认只有一个，用Open([Template])创建

        private bool _IsVisibledExcel;								//打印或预览时是否还要显示Excel窗体
        private string _FormCaption;								//打印预览Excel窗体的标题栏

        private Object oMissing = Missing.Value;  //实例化参数对象

        #region _xlApp、_xlWorkbook、IsVisibledExcel、FormCaption属性
        /// <summary>
        /// Excel应用程序
        /// </summary>
        public Application Application
        {
            get
            {
                return _xlApp;
            }
        }

        /// <summary>
        /// Excel工作薄，默认只有一个，用Open([Template])创建
        /// </summary>
        public Workbook Workbooks
        {
            get
            {
                return _xlWorkbook;
            }
        }

        /// <summary>
        /// 打印或预览时是否还要显示Excel窗体
        /// </summary>
        public bool IsVisibledExcel
        {
            get
            {
                return _IsVisibledExcel;
            }
            set
            {
                _IsVisibledExcel = value;
            }
        }

        /// <summary>
        /// 打印预览Excel窗体的标题栏
        /// </summary>
        public string FormCaption
        {
            get
            {
                return _FormCaption;
            }
            set
            {
                _FormCaption = value;
            }
        }

        #endregion

        /// <summary>
        /// 创建立Excel新的实例
        /// </summary>
        public ExcelBase()
        {
            _IsVisibledExcel = false;				//打印及预览时Excel显示
            _FormCaption = Resources.打印预览;

            //应检查Excell进程是否已在运行，否则每次实例化一个，则Excell进程多一个。现在在Close()里进行强制垃圾回收，可以不检测了。
            try
            {
                _xlApp = new ApplicationClass();
            }
            catch (Exception ex)
            {
                throw new ExceptionExcelCreateInstance(Resources.ExcelBase + ex.Message);
            }

            _xlApp.DisplayAlerts = false;			 //关闭程序建立的Excel文件时，不会提示是否要保存修改
        }

        #region 打开关闭
        /// <summary>
        /// 打开Excel，并建立默认的Workbooks。
        /// </summary>
        /// <returns></returns>
        public void Open()
        {
            //打开并新建立默认的Excel
            //Workbooks.Add([template]) As Workbooks

            try
            {
                _xlWorkbook = _xlApp.Workbooks.Add(oMissing);
            }
            catch (Exception ex)
            {
                throw new ExceptionExcelOpen(Resources.Open + ex.Message);
            }

        }

        /// <summary>
        /// 根据现有工作薄模板打开，如果指定的模板不存在，则用默认的空模板
        /// </summary>
        /// <param name="p_templateFileName">用作模板的工作薄文件名</param>
        public void Open(string p_templateFileName)
        {
            if (File.Exists(p_templateFileName))
            {
                //用模板打开
                //Workbooks.Add Template:="C:\tpt.xlt"

                try
                {
                    _xlWorkbook = _xlApp.Workbooks.Add(p_templateFileName);
                }
                catch (Exception ex)
                {
                    throw new ExceptionExcelOpen(Resources.Open + ex.Message);
                }
            }
            else
            {
                Open();
            }
        }

        /// <summary>
        /// 关闭
        /// </summary>
        public void Close()
        {

            _xlApp.Workbooks.Close();
            _xlWorkbook = null;

            _xlApp.Quit();
            _xlApp = null;

            oMissing = null;

            //强制垃圾回收，否则每次实例化Excel，则Excell进程多一个。
            GC.Collect();
        }
        #endregion

        #region PrintPreview()、Print()用Excel打印、预览，如果要显示Excel窗口，请设置IsVisibledExcel
        /// <summary>
        /// 显示Excel
        /// </summary>
        public void ShowExcel()
        {
            _xlApp.Visible = true;
        }

        /// <summary>
        /// 用Excel打印预览，如果要显示Excel窗口，请设置IsVisibledExcel 
        /// </summary>
        public void PrintPreview()
        {
            _xlApp.Caption = _FormCaption;
            _xlApp.Visible = true;

            try
            {
                _xlApp.ActiveWorkbook.PrintPreview(oMissing);

            }
            catch { }

            _xlApp.Visible = this.IsVisibledExcel;

        }

        /// <summary>
        /// 用Excel打印，如果要显示Excel窗口，请设置IsVisibledExcel 
        /// </summary>
        public void Print()
        {
            _xlApp.Visible = this.IsVisibledExcel;

            //Object oMissing = System.Reflection.Missing.Value;  //实例化参数对象
            try
            {
                _xlApp.ActiveWorkbook.PrintOut(oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
            }
            catch { }
        }
        #endregion

        #region 另存
        /// <summary>
        /// 另存。如果保存成功，则返回true，否则，如果保存不成功或者如果已存在文件但是选择了不替换也返回false
        /// </summary>
        /// <param name="p_fileName">将要保存的文件名</param>
        /// <param name="p_ReplaceExistsFileName">如果文件存在，则替换</param>
        public bool SaveAs(string p_fileName, bool p_ReplaceExistsFileName)
        {
            bool blnReturn = false;
            if (File.Exists(p_fileName))
            {
                if (p_ReplaceExistsFileName)
                {
                    try
                    {
                        File.Delete(p_fileName);
                        blnReturn = true;
                    }
                    catch (Exception ex)
                    {
                        string strErr = ex.Message;
                    }
                }
            }

            try
            {

                _xlApp.ActiveWorkbook.SaveCopyAs(p_fileName);
                blnReturn = true;
            }
            catch
            {
                blnReturn = false;
            }

            return blnReturn;
        }
        #endregion


        //核心函数，GetRange()，获取指定范围内的单元格
        /*
        public Range GetRange(int p_rowIndex,int p_colIndex)
        public Range GetRange(int p_rowIndex,string p_colChars)
        public Range GetRange(int p_startRowIndex,int p_startColIndex,int p_endRowIndex,int p_endColIndex)
        public Range GetRange(int p_startRowIndex,string p_startColChars,int p_endRowIndex,string p_endColChars)
        */

        #region GetRange，如Range("B10")，Range("C8:F11")，Range(2,10)，Range(2,"A")，Range(3,8,6,11)，Range(3,"A",6,"F")
        /// <summary>
        /// 获取指定单元格或指定范围内的单元格，行索引为从1开始的数字，最大65536，列索引为A~Z、AA~AZ、BA~BZ...HA~HZ、IA~IV的字母及组合，也可以是1-65536数字。
        /// </summary>
        /// <param name="p_rowIndex">单元格行索引，从1开始</param>
        /// <param name="p_colIndex">单元格列索引，从1开始，列索引也可以用字母A到Z或字母组合AA~AZ，最大IV的Excel字母索引</param>
        /// <returns></returns>
        public Range GetRange(int p_rowIndex, int p_colIndex)
        {
            //单个	Range(10,3).Select		//第10行3列
            return GetRange(p_rowIndex, p_colIndex, p_rowIndex, p_colIndex);
        }

        /// <param name="p_colChars">单元格列字母及组合索引，从A开始</param>
        public Range GetRange(int p_rowIndex, string p_colChars)
        {
            //单个	Range("C10").Select		//第10行3列			
            return GetRange(p_rowIndex, p_colChars, p_rowIndex, p_colChars);
        }

        /// <param name="p_startRowIndex">指定单元范围起始行索引，从1开始</param>
        /// <param name="p_startColIndex">指定单元范围起始列数字索引，从1开始</param>
        /// <param name="p_endRowIndex">指定单元范围结束行索引</param>
        /// <param name="p_endColIndex">指定单元范围结束列数字索引</param>
        public Range GetRange(int p_startRowIndex, int p_startColIndex, int p_endRowIndex, int p_endColIndex)
        {
            Range range = _xlApp.get_Range(_xlApp.Cells[p_startRowIndex, p_startColIndex], _xlApp.Cells[p_endRowIndex, p_endColIndex]);

            return range;
        }

        /// <param name="p_startColChars">指定单元范围起始列字母及组合索引</param>
        /// <param name="p_endColChars">指定单元范围结束列字母及组合索引</param>
        public Range GetRange(int p_startRowIndex, string p_startColChars, int p_endRowIndex, string p_endColChars)
        {
            //矩形	Range("D8:F11").Select

            Range range = _xlApp.get_Range(p_startColChars + p_startRowIndex.ToString(), p_endColChars + p_endRowIndex.ToString());

            return range;
        }

        #endregion

        #region MergeCells(Range p_Range)合并单元格，合并后，默认居中
        /// <summary>
        /// 合并指定范围内单元格，合并后，默认居中
        /// </summary>
        /// <param name="p_Range"></param>
        public void MergeCells(Range p_Range)
        {
            p_Range.HorizontalAlignment = Constants.xlCenter;
            p_Range.VerticalAlignment = Constants.xlCenter;
            p_Range.WrapText = false;
            p_Range.Orientation = 0;
            p_Range.AddIndent = false;
            p_Range.IndentLevel = 0;
            p_Range.ShrinkToFit = false;
            //p_Range.ReadingOrder = Constants.xlContext;
            p_Range.MergeCells = false;
            p_Range.Merge(oMissing);

            //    With Selection
            //        .HorizontalAlignment = xlCenter
            //        .VerticalAlignment = xlCenter
            //        .WrapText = False
            //        .Orientation = 0
            //        .AddIndent = False
            //        .IndentLevel = 0
            //        .ShrinkToFit = False
            //        .ReadingOrder = xlContext
            //        .MergeCells = False
            //    End With
            //    Selection.Merge
        }
        #endregion


        #region 插入分页符，暂无实现
        /// <summary>
        /// 在指定的行上插入分页符
        /// </summary>
        /// <param name="p_rowIndex">行索引</param>
        public void InsertVPageBreaks(int p_rowIndex)
        {
            Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)this.Workbooks.ActiveSheet;
            ws.HPageBreaks.Add(this.GetRange(p_rowIndex, "A"));
        }

        public void InsertHPageBreaks(int p_colIndex)
        {

        }

        public void InsertHPageBreaks(string p_colChars)
        {

        }
        #endregion

        #region 插入整行、整列InsertRow(int p_rowIndex)、InsertColumn(int p_colIndex)、InsertColumn(string p_colChars)
        /// <summary>
        /// 在指定的行上插入一整行
        /// </summary>
        /// <param name="p_rowIndex">行索引</param>
        public void InsertRow(int p_rowIndex)
        {
            //    Rows("2:2").Select
            //    Selection.Insert Shift:=xlDown

            Range range;

            range = GetRange(p_rowIndex, "A");
            range.Select();

            //Excel2003支持两参数
            range.EntireRow.Insert(oMissing, oMissing);

            //Excel2000支持一个参数，经过测试，用Interop.ExcelV1.3(Excel2000)，可以正常运行在Excel2003中
            //range.EntireRow.Insert(oMissing);
        }

        /// <summary>
        /// 用模板行在指定的行上插入，即Excel的插入复制单元格
        /// </summary>
        /// <param name="p_rowIndex"></param>
        /// <param name="p_templateRowIndex"></param>
        public void InsertRow(int p_rowIndex, int p_templateRowIndex)
        {
            Range range;
            range = (Range)_xlApp.Rows[p_templateRowIndex.ToString() + Resources.Range + p_templateRowIndex.ToString(), oMissing];
            range.Select();
            range.Copy(oMissing);

            InsertRow(p_rowIndex);
        }

        /// <summary>
        /// 在指定的列上插入一整列
        /// </summary>
        /// <param name="p_colIndex">列索引</param>
        public void InsertColumn(int p_colIndex)
        {
            Range range = GetRange(1, p_colIndex);
            range.Select();

            //Excel2003支持两参数
            range.EntireColumn.Insert(oMissing, oMissing);
            //Excel2000支持一个参数
            //range.EntireColumn.Insert(oMissing);
        }

        /// <summary>
        /// 在指定的列上插入一整列
        /// </summary>
        /// <param name="p_colChars">列字母或组合</param>
        public void InsertColumn(string p_colChars)
        {
            Range range = GetRange(1, p_colChars);
            range.Select();
            //Excel2003支持两参数
            range.EntireColumn.Insert(oMissing, oMissing);
            //Excel2000支持一个参数
            //range.EntireColumn.Insert(oMissing);
        }
        #endregion

        #region 删除整行、整列DeleteRow(int p_rowIndex)、DeleteColumn(int p_colIndex)、DeleteColumn(string p_colChars)
        /// <summary>
        /// 删除指定的整行
        /// </summary>
        /// <param name="p_rowIndex">行索引</param>
        public void DeleteRow(int p_rowIndex)
        {
            Range range;

            range = GetRange(p_rowIndex, "A");
            range.Select();
            range.EntireRow.Delete(oMissing);
        }

        /// <summary>
        /// 删除指定的整列
        /// </summary>
        /// <param name="p_colIndex">列索引</param>
        public void DeleteColumn(int p_colIndex)
        {
            Range range;

            range = GetRange(1, p_colIndex);
            range.Select();
            range.EntireColumn.Delete(oMissing);
        }

        /// <summary>
        /// 删除指定的整列
        /// </summary>
        /// <param name="p_colChars">列字母或组合</param>
        public void DeleteColumn(string p_colChars)
        {
            Range range;

            range = GetRange(1, p_colChars);
            range.Select();
            range.EntireColumn.Delete(oMissing);
        }
        #endregion

        #region 设置行高列宽SetRowHeight(int p_rowIndex,float p_rowHeight)、SetColumnWidth(int p_colIndex,float p_colWidth)、SetColumnWidth(string p_colChars,float p_colWidth)
        public void SetRowHeight(int p_rowIndex, float p_rowHeight)
        {
            Range range;

            range = GetRange(p_rowIndex, "A");
            range.Select();
            range.RowHeight = p_rowHeight;
        }

        public void SetColumnWidth(int p_colIndex, float p_colWidth)
        {
            Range range;

            range = GetRange(1, p_colIndex);
            range.Select();
            range.ColumnWidth = p_colWidth;
        }

        public void SetColumnWidth(string p_colChars, float p_colWidth)
        {
            Range range;

            range = GetRange(1, p_colChars);
            range.Select();
            range.ColumnWidth = p_colWidth;
        }

        #endregion

        #region 设置自动列宽


        public void SetColumnWidth(int p_colIndex)
        {
            Range range;

            range = GetRange(1, p_colIndex);
            range.Select();
            range.EntireColumn.AutoFit();
        }

        public void SetColumnWidth(string p_colChars)
        {
            Range range;

            range = GetRange(1, p_colChars);
            range.Select();
            range.EntireColumn.AutoFit();
        }

        #endregion


        #region SetFont(Range p_Range,Font p_Font[,Color p_color])
        public void SetFont(Range p_Range, Font p_Font)
        {
            SetFont(p_Range, p_Font, Color.Black);
        }

        public void SetFont(Range p_Range, Font p_Font, Color p_color)
        {
            p_Range.Select();
            p_Range.Font.Name = p_Font.Name;
            p_Range.Font.Size = p_Font.Size;

            //p_Range.Font.Color = p_color;

            p_Range.Font.Bold = p_Font.Bold;
            p_Range.Font.Italic = p_Font.Italic;

            p_Range.Font.Strikethrough = p_Font.Strikeout;
            p_Range.Font.Underline = p_Font.Underline;
        }
        #endregion

        #region SetBordersEdge 设置指定范围边框（左、顶、右、底、往右下对角线、往右上对角线、内部水平线、内部垂直线、无线）线，并可指定线条的样式（无、虚线、点线等）及线粗细
        /// <summary>
        /// 用连续的普通粗细的线设置指定范围内的边界
        /// </summary>
        /// <param name="p_Range"></param>
        /// <param name="p_BordersEdge"></param>
        public void SetBordersEdge(Range p_Range, BordersEdge p_BordersEdge)
        {
            SetBordersEdge(p_Range, p_BordersEdge, BordersLineStyle.xlContinuous, BordersWeight.xlThin);
        }

        public void SetBordersEdge(Range p_Range, BordersEdge p_BordersEdge, BordersLineStyle p_BordersLineStyle, BordersWeight p_BordersWeight)
        {
            p_Range.Select();

            Border border = null;

            switch (p_BordersEdge)
            {
                //左右顶底的线
                case BordersEdge.xlLeft:
                    border = p_Range.Borders[XlBordersIndex.xlEdgeLeft];
                    break;
                case BordersEdge.xlRight:
                    border = p_Range.Borders[XlBordersIndex.xlEdgeRight];
                    break;
                case BordersEdge.xlTop:
                    border = p_Range.Borders[XlBordersIndex.xlEdgeTop];
                    break;
                case BordersEdge.xlBottom:
                    border = p_Range.Borders[XlBordersIndex.xlEdgeBottom];
                    break;
                //对角线
                case BordersEdge.xlDiagonalDown:
                    border = p_Range.Borders[XlBordersIndex.xlDiagonalDown];
                    break;
                case BordersEdge.xlDiagonalUp:
                    border = p_Range.Borders[XlBordersIndex.xlDiagonalUp];
                    break;
                //边框内部是横竖线(不包括边框)
                case BordersEdge.xlInsideHorizontal:
                    border = p_Range.Borders[XlBordersIndex.xlInsideHorizontal];
                    break;
                case BordersEdge.xlInsideVertical:
                    border = p_Range.Borders[XlBordersIndex.xlInsideVertical];
                    break;
                case BordersEdge.xlLineStyleNone:
                    //所先范围内所有线都没有
                    p_Range.Borders[XlBordersIndex.xlDiagonalDown].LineStyle = XlLineStyle.xlLineStyleNone;		//xlNone
                    p_Range.Borders[XlBordersIndex.xlDiagonalUp].LineStyle = XlLineStyle.xlLineStyleNone;
                    p_Range.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlLineStyleNone;
                    p_Range.Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlLineStyleNone;
                    p_Range.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlLineStyleNone;
                    p_Range.Borders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlLineStyleNone;
                    p_Range.Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlLineStyleNone;
                    p_Range.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlLineStyleNone;
                    break;
            }

            if (border != null)
            {
                //XlLineStyle
                XlLineStyle mXlLineStyle = XlLineStyle.xlContinuous;
                switch (p_BordersLineStyle)
                {
                    case BordersLineStyle.xlContinuous:
                        mXlLineStyle = XlLineStyle.xlContinuous;
                        break;
                    case BordersLineStyle.xlDash:
                        mXlLineStyle = XlLineStyle.xlDash;
                        break;
                    case BordersLineStyle.xlDashDot:
                        mXlLineStyle = XlLineStyle.xlDashDot;
                        break;
                    case BordersLineStyle.xlDashDotDot:
                        mXlLineStyle = XlLineStyle.xlDashDotDot;
                        break;
                    case BordersLineStyle.xlDot:
                        mXlLineStyle = XlLineStyle.xlDot;
                        break;
                    case BordersLineStyle.xlDouble:
                        mXlLineStyle = XlLineStyle.xlDouble;
                        break;
                    case BordersLineStyle.xlLineStyleNone:
                        mXlLineStyle = XlLineStyle.xlLineStyleNone;
                        break;
                    case BordersLineStyle.xlSlantDashDot:
                        mXlLineStyle = XlLineStyle.xlSlantDashDot;
                        break;
                }
                border.LineStyle = mXlLineStyle;

                //XlBorderWeight
                XlBorderWeight mXlBorderWeight = XlBorderWeight.xlThin;

                switch (p_BordersWeight)
                {
                    case BordersWeight.xlHairline:
                        mXlBorderWeight = XlBorderWeight.xlHairline;
                        break;
                    case BordersWeight.xlMedium:
                        mXlBorderWeight = XlBorderWeight.xlMedium;
                        break;
                    case BordersWeight.xlThick:
                        mXlBorderWeight = XlBorderWeight.xlThick;
                        break;
                    case BordersWeight.xlThin:
                        mXlBorderWeight = XlBorderWeight.xlThin;
                        break;
                }
                border.Weight = mXlBorderWeight;

            }//End IF

        }
        #endregion

        #region ClearBordersEdge，清除指定范围内的所有线，以SetBordersEdge设置边框为基础
        public void ClearBordersEdge(Range p_Range)
        {
            SetBordersEdge(p_Range, BordersEdge.xlLineStyleNone);
        }
        #endregion

        #region GetCellText(p_Range])
        public string GetCellText(Range p_Range)
        {
            string strReturn = "";
            strReturn = p_Range.Text.ToString();
            return strReturn;
        }
        #endregion


        #region SetCellText(Range)
        public void SetCellText(Range p_Range, string p_text)
        {
            p_Range.Cells.FormulaR1C1 = p_text;
        }
        #endregion


    }//End class

    /// <summary>
    /// 创建Excel类实例时错误
    /// </summary>
    public class ExceptionExcelCreateInstance : Exception
    {
        #region 实现...
        string _Message = Resources._Message;

        public ExceptionExcelCreateInstance()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //		
        }

        public ExceptionExcelCreateInstance(string message)
        {
            this._Message = message;
        }

        public override string Message
        {
            get
            {
                return this._Message;
            }
        }
        #endregion

    }//End ExceptionExcelCreateInstance


    /// <summary>
    /// 打开Excel时错误
    /// </summary>
    public class ExceptionExcelOpen : Exception
    {
        #region 实现...
        string _Message = Resources.打开Excel时错误;

        public ExceptionExcelOpen()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //		
        }

        public ExceptionExcelOpen(string message)
        {
            this._Message = message;
        }

        public override string Message
        {
            get
            {
                return this._Message;
            }
        }
        #endregion
    }

    /// <summary>
    /// 该类主要定义Excel的程序对象，启动Excel并打印及保存。可能依赖于Interop.VBIDE及Interop.Microsoft.Office.Core，如果需要，请加上。
    /// 
    /// </summary>
    public class ExcelAccess : ExcelBase
    {
        private readonly Object oMissing = Missing.Value;
        public ExcelAccess()
        {

        }

        //核心函数，GetRange()，获取指定范围内的单元格
        /*
        public Range GetRange(int p_rowIndex,int p_colIndex)
        public Range GetRange(int p_rowIndex,string p_colChars)
        public Range GetRange(int p_startRowIndex,int p_startColIndex,int p_endRowIndex,int p_endColIndex)
        public Range GetRange(int p_startRowIndex,string p_startColChars,int p_endRowIndex,string p_endColChars)
        */

        #region MergeCells()合并单元格，合并后，默认居中，用Range或它的指定范围作为参数
        /// <summary>
        /// 合并指定范围内单元格
        /// </summary>
        /// <param name="p_rowIndex">行索引，也可以指定起始行、终止行索引</param>
        /// <param name="p_colIndex">列索引，也可以指定起始列、终止列数字或字母及组合索引</param>
        /// 

        /// <summary>
        /// 合并指定范围内单元格
        /// </summary>
        /// <param name="p_startRowIndex">起始行索引</param>
        /// <param name="p_startColIndex">起始列索引，可以是数字或字母及组合索引</param>
        /// <param name="p_endRowIndex">结束行索引</param>
        /// <param name="p_endColIndex">结束列索引，可以是数字或字母及组合索引</param>
        public void MergeCells(int p_startRowIndex, int p_startColIndex, int p_endRowIndex, int p_endColIndex)
        {
            MergeCells(GetRange(p_startRowIndex, p_startColIndex, p_endRowIndex, p_endColIndex));
        }

        /// <summary>
        /// 合并指定范围内单元格
        /// </summary>
        /// <param name="p_startRowIndex">起始行索引</param>
        /// <param name="p_startColChars">起始列索引，可以是数字或字母及组合索引</param>
        /// <param name="p_endRowIndex">结束行索引</param>
        /// <param name="p_endColChars">结束列索引，可以是数字或字母及组合索引</param>
        public void MergeCells(int p_startRowIndex, string p_startColChars, int p_endRowIndex, string p_endColChars)
        {
            MergeCells(GetRange(p_startRowIndex, p_startColChars, p_endRowIndex, p_endColChars));
        }
        #endregion

        #region SetFont(Range p_Range,Font p_Font[,Color p_color])

        public void SetFont(int p_startRowIndex, int p_startColIndex, int p_endRowIndex, int p_endColIndex, Font p_Font)
        {
            SetFont(GetRange(p_startRowIndex, p_startColIndex, p_endRowIndex, p_endColIndex), p_Font, Color.Black);
        }
        #endregion

        #region SetBordersEdge 设置指定范围边框（左、顶、右、底、往右下对角线、往右上对角线、内部水平线、内部垂直线、无线）线，并可指定线条的样式（无、虚线、点线等）及线粗细
        /// <summary>
        /// 设置指定范围边框（左、顶、右、底、往右下对角线、往右上对角线、内部水平线、内部垂直线、无线）线，并可指定线的样式及线粗细
        /// </summary>
        /// <param name="p_rowIndex">行索引，也可以指定起始行、终止行索引</param>
        /// <param name="p_colIndex">列索引，也可以指定起始列、终止列数字或字母及组合索引</param>
        /// <param name="p_BordersEdge">边框：左、顶、右、底、往右下对角线、往右上对角线、内部水平线、内部垂直线、无线</param>
        /// <param name="p_BordersLineStyle">线条样式：无、虚线、点线等，看Excel便知</param>
        /// <param name="p_BordersWeight">粗细</param>
        public void SetBordersEdge(int p_rowIndex, int p_colIndex, BordersEdge p_BordersEdge, BordersLineStyle p_BordersLineStyle, BordersWeight p_BordersWeight)
        {
            SetBordersEdge(GetRange(p_rowIndex, p_colIndex), p_BordersEdge, p_BordersLineStyle, p_BordersWeight);
        }

        public void SetBordersEdge(int p_rowIndex, string p_colChars, BordersEdge p_BordersEdge, BordersLineStyle p_BordersLineStyle, BordersWeight p_BordersWeight)
        {
            SetBordersEdge(GetRange(p_rowIndex, p_colChars), p_BordersEdge, p_BordersLineStyle, p_BordersWeight);
        }

        public void SetBordersEdge(int p_startRowIndex, int p_startColIndex, int p_endRowIndex, int p_endColIndex, BordersEdge p_BordersEdge, BordersLineStyle p_BordersLineStyle, BordersWeight p_BordersWeight)
        {
            SetBordersEdge(GetRange(p_startRowIndex, p_startColIndex, p_endRowIndex, p_endColIndex), p_BordersEdge, p_BordersLineStyle, p_BordersWeight);
        }

        public void SetBordersEdge(int p_startRowIndex, string p_startColChars, int p_endRowIndex, string p_endColChars, BordersEdge p_BordersEdge, BordersLineStyle p_BordersLineStyle, BordersWeight p_BordersWeight)
        {
            SetBordersEdge(GetRange(p_startRowIndex, p_startColChars, p_endRowIndex, p_endColChars), p_BordersEdge, p_BordersLineStyle, p_BordersWeight);
        }

        /// <summary>
        /// 设置指定范围内边界及内部网格线
        /// </summary>
        /// <param name="p_startRowIndex"></param>
        /// <param name="p_startColIndex"></param>
        /// <param name="p_endRowIndex"></param>
        /// <param name="p_endColIndex"></param>
        /// <param name="p_endColIndex">IsBordersOrBordersGrid，true只输出四周的边框，否则输出边框与网格线</param>
        public void SetBordersEdge(int p_startRowIndex, int p_startColIndex, int p_endRowIndex, int p_endColIndex, bool IsBordersOrBordersGrid)
        {
            SetBordersEdge(GetRange(p_startRowIndex, p_startColIndex, p_endRowIndex, p_endColIndex), BordersEdge.xlLeft);
            SetBordersEdge(GetRange(p_startRowIndex, p_startColIndex, p_endRowIndex, p_endColIndex), BordersEdge.xlTop);
            SetBordersEdge(GetRange(p_startRowIndex, p_startColIndex, p_endRowIndex, p_endColIndex), BordersEdge.xlRight);
            SetBordersEdge(GetRange(p_startRowIndex, p_startColIndex, p_endRowIndex, p_endColIndex), BordersEdge.xlBottom);

            if (!IsBordersOrBordersGrid)
            {
                SetBordersEdge(GetRange(p_startRowIndex, p_startColIndex, p_endRowIndex, p_endColIndex), BordersEdge.xlInsideHorizontal);
                SetBordersEdge(GetRange(p_startRowIndex, p_startColIndex, p_endRowIndex, p_endColIndex), BordersEdge.xlInsideVertical);
            }
        }
        #endregion

        #region ClearBordersEdge，清除指定范围内的所有线，以SetBordersEdge设置边框为基础
        /// <summary>
        /// 清除用SetBordersEdge设置的边框内的所有线
        /// </summary>
        /// <param name="p_rowIndex">行索引，也可以指定起始行、终止行索引</param>
        /// <param name="p_colIndex">列索引，也可以指定起始列、终止列数字或字母及组合索引</param>
        public void ClearBordersEdge(int p_rowIndex, int p_colIndex)
        {
            SetBordersEdge(GetRange(p_rowIndex, p_colIndex), BordersEdge.xlLineStyleNone);
        }

        public void ClearBordersEdge(int p_rowIndex, string p_colChars)
        {
            SetBordersEdge(GetRange(p_rowIndex, p_colChars), BordersEdge.xlLineStyleNone);
        }

        public void ClearBordersEdge(int p_startRowIndex, int p_startColIndex, int p_endRowIndex, int p_endColIndex)
        {
            SetBordersEdge(GetRange(p_startRowIndex, p_startColIndex, p_endRowIndex, p_endColIndex), BordersEdge.xlLineStyleNone);
        }

        public void ClearBordersEdge(int p_startRowIndex, string p_startColChars, int p_endRowIndex, string p_endColChars)
        {
            SetBordersEdge(GetRange(p_startRowIndex, p_startColChars, p_endRowIndex, p_endColChars), BordersEdge.xlLineStyleNone);
        }
        #endregion

        #region GetCellText(int p_rowIndex,int p_colIndex[/string p_colChars])，用Range或它的指定范围作为参数
        public string GetCellText(int p_rowIndex, int p_colIndex)
        {
            string strReturn = "";

            Range range = GetRange(p_rowIndex, p_colIndex);

            strReturn = range.Text.ToString();

            range = null;

            return strReturn;
        }

        public string GetCellText(int p_rowIndex, string p_colChars)
        {
            string strReturn = "";

            Range range = GetRange(p_rowIndex, p_colChars);

            strReturn = range.Text.ToString();

            range = null;

            return strReturn;
        }
        #endregion


        #region SetCellText(...)，参数对应于Range(...)，可以一个单元格也可以区域内的单元格一起设置同样的文本。用Range或它的指定范围作为参数
        public void SetCellText(int p_rowIndex, int p_colIndex, string p_text)
        {
            //			xlApp.Cells[p_rowIndex,p_colIndex] = p_text;			
            Range range = GetRange(p_rowIndex, p_colIndex);
            range.Cells.FormulaR1C1 = p_text;
            range = null;
        }

        public void SetCellText(int p_rowIndex, string p_colChars, string p_text)
        {
            Range range = GetRange(p_rowIndex, p_colChars);
            range.Cells.FormulaR1C1 = p_text;
            range = null;
        }

        public void SetCellText(int p_startRowIndex, int p_startColIndex, int p_endRowIndex, int p_endColIndex, string p_text)
        {
            Range range = GetRange(p_startRowIndex, p_startColIndex, p_endRowIndex, p_endColIndex);
            range.Cells.FormulaR1C1 = p_text;
            range = null;
        }

        public void SetCellText(int p_startRowIndex, string p_startColChars, int p_endRowIndex, string p_endColChars, string p_text)
        {
            Range range = GetRange(p_startRowIndex, p_startColChars, p_endRowIndex, p_endColChars);
            range.Cells.FormulaR1C1 = p_text;
            range = null;
        }



        public void SetCellText(DataTable p_DataTable, int p_startExcelRowIndex, int p_startExcelColIndex, bool IsDrawGridLine)
        {

            for (int i = 0; i < p_DataTable.Rows.Count; i++)
            {
                for (int j = 0; j < p_DataTable.Columns.Count; j++)
                {

                    SetCellText(p_startExcelRowIndex + i, p_startExcelColIndex + j, p_DataTable.Rows[i][j].ToString());
                }
            }
            if (IsDrawGridLine)
            {
                SetBordersEdge(p_startExcelRowIndex, p_startExcelColIndex, p_startExcelRowIndex + p_DataTable.Rows.Count - 1, p_startExcelColIndex + p_DataTable.Columns.Count - 1, false);
            }
        }

        public void SetCellText(DataTable p_DataTable, int p_maxrow, int p_maxcol)
        {

            Range m_objRange;
            try
            {
                foreach (DataRow dr in p_DataTable.Rows)
                {
                    for (int col = 0; col < p_DataTable.Columns.Count; col++)
                    {
                        for (int excelcol = 1; excelcol < p_maxcol; excelcol++)
                        {
                            for (int excelrow = 1; excelrow < p_maxrow; excelrow++)
                            {
                                //string excelColName = ExcelColNumberToColText(excelcol);

                                m_objRange = GetRange(excelrow, excelcol);

                                if (m_objRange.Text.ToString().Replace("$", "") == p_DataTable.Columns[col].ColumnName)
                                {
                                    m_objRange.Value2 = dr[col].ToString();
                                }
                            }

                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public void SetCellText(DataTable p_DataTable, Range range)
        {
            object[,] dataArray = new object[p_DataTable.Rows.Count, p_DataTable.Columns.Count];

            for (int i = 0; i < p_DataTable.Rows.Count; i++)
            {
                for (int j = 0; j < p_DataTable.Columns.Count; j++)
                {
                    dataArray[i, j] = p_DataTable.Rows[i][j].ToString();
                }
            }

            range.Value2 = dataArray;
        }

        #endregion

        #region InsertPicture(...)，参数对应于Range(...)，可以一个单元格也可以区域内的单元格一起设置同样的图像。用Range或它的指定范围作为参数
        public void InsertPicture(int p_rowIndex, int p_colIndex, string PicturePath)
        {
            //			xlApp.Cells[p_rowIndex,p_colIndex] = p_text;	
            Worksheet m_objWorkSheet = (Worksheet)base.Workbooks.ActiveSheet;
            Range m_objRange = GetRange(p_rowIndex, p_colIndex);

            m_objRange.Select();
            Pictures pics = (Pictures)m_objWorkSheet.Pictures(oMissing);
            Picture pic = pics.Insert(PicturePath, oMissing);
            //double v_fFactor = 1;
            ////设置图片大小
            //if (pic.Width * (double)m_objRange.Height > pic.Height * (double)m_objRange.Width)
            //    v_fFactor = (double)m_objRange.Width / (double)pic.Width;
            //else
            //    v_fFactor = (double)m_objRange.Height / (double)pic.Height;
            //pic.Left = (double)m_objRange.Left + ((double)m_objRange.Width - (pic.Width * v_fFactor)) / 2 + 1;
            //pic.Top = (double)m_objRange.Top + ((double)m_objRange.Height - (pic.Height * v_fFactor)) / 2 + 1;
            //pic.Width = pic.Width * v_fFactor - 0.5d;
            //pic.Height = pic.Height * v_fFactor - 0.5d;
            pic.Left = (double)m_objRange.Left;
            pic.Top = (double)m_objRange.Top;
            if ((double)m_objRange.Height > (double)m_objRange.Width)
            {
                pic.Height = (double)m_objRange.Width;
                pic.Width = (double)m_objRange.Width;
            }
            else
            {
                pic.Width = (double)m_objRange.Height;
                pic.Height = (double)m_objRange.Height;
            }

            m_objRange = null;
        }

        public void InsertPicture(int p_rowIndex, string p_colChars, string PicturePath)
        {
            Worksheet m_objWorkSheet = (Worksheet)base.Workbooks.ActiveSheet;
            Range m_objRange = GetRange(p_rowIndex, p_colChars);

            m_objRange.Select();
            Pictures pics = (Pictures)m_objWorkSheet.Pictures(oMissing);
            Picture pic = pics.Insert(PicturePath, oMissing);
            double v_fFactor = 1;
            //设置图片大小
            if (pic.Width * (double)m_objRange.Height > pic.Height * (double)m_objRange.Width)
                v_fFactor = (double)m_objRange.Width / (double)pic.Width;
            else
                v_fFactor = (double)m_objRange.Height / (double)pic.Height;
            pic.Left = (double)m_objRange.Left + ((double)m_objRange.Width - (pic.Width * v_fFactor)) / 2 + 1;
            pic.Top = (double)m_objRange.Top + ((double)m_objRange.Height - (pic.Height * v_fFactor)) / 2 + 1;
            pic.Width = pic.Width * v_fFactor - 0.5d;
            pic.Height = pic.Height * v_fFactor - 0.5d;
            m_objRange = null;
        }

        public void InsertPicture(int p_startRowIndex, int p_startColIndex, int p_endRowIndex, int p_endColIndex, string PicturePath)
        {
            Worksheet m_objWorkSheet = (Worksheet)base.Workbooks.ActiveSheet;
            Range m_objRange = GetRange(p_startRowIndex, p_startColIndex, p_endRowIndex, p_endColIndex);

            m_objRange.Select();
            Pictures pics = (Pictures)m_objWorkSheet.Pictures(oMissing);
            Picture pic = pics.Insert(PicturePath, oMissing);
            double v_fFactor = 1;
            //设置图片大小
            if (pic.Width * (double)m_objRange.Height > pic.Height * (double)m_objRange.Width)
                v_fFactor = (double)m_objRange.Width / (double)pic.Width;
            else
                v_fFactor = (double)m_objRange.Height / (double)pic.Height;
            //pic.Left = (double)m_objRange.Left + ((double)m_objRange.Width - (pic.Width * v_fFactor)) / 2 + 1;
            //pic.Top = (double)m_objRange.Top + ((double)m_objRange.Height - (pic.Height * v_fFactor)) / 2 + 1;
            pic.Width = pic.Width * v_fFactor - 1d;
            //pic.ShapeRange.Apply();
            pic.Height = pic.Height * v_fFactor - 1d;
            //pic.ShapeRange.Apply();
            pic.Left = (double)m_objRange.Left + ((double)m_objRange.Width - pic.Width) / 2 + 1;
            pic.Top = (double)m_objRange.Top + ((double)m_objRange.Height - pic.Height) / 2 + 1;
            m_objRange = null;
        }

        public void InsertPicture(int p_startRowIndex, string p_startColChars, int p_endRowIndex, string p_endColChars, string PicturePath)
        {

            Worksheet m_objWorkSheet = (Worksheet)base.Workbooks.ActiveSheet;
            Range m_objRange = GetRange(p_startRowIndex, p_startColChars, p_endRowIndex, p_endColChars);

            m_objRange.Select();
            Pictures pics = (Pictures)m_objWorkSheet.Pictures(oMissing);
            Picture pic = pics.Insert(PicturePath, oMissing);
            //pic.Left = (double)m_objRange.Left;
            //pic.Top = (double)m_objRange.Top;
            //if ((double)m_objRange.Height > (double)m_objRange.Width)
            //{
            //    pic.Height = (double)m_objRange.Width;
            //    pic.Width = (double)m_objRange.Width;
            //}
            //else
            //{
            //    pic.Width = (double)m_objRange.Height;
            //    pic.Height = (double)m_objRange.Height;
            //}

            //m_objRange = null;
            double v_fFactor = 1;
            //设置图片大小
            if (pic.Width * (double)m_objRange.Height > pic.Height * (double)m_objRange.Width)
                v_fFactor = (double)m_objRange.Width / (double)pic.Width;
            else
                v_fFactor = (double)m_objRange.Height / (double)pic.Height;

            double version = Convert.ToDouble(this.Application.Version);
            if (version >= 12.0)
            {
                pic.Width = pic.Width * v_fFactor - 0.5d;
                pic.ShapeRange.Apply();
                pic.Height = pic.Height * v_fFactor - 0.5d;
                pic.ShapeRange.Apply();
            }
            else
            {
                pic.Width = pic.Width * v_fFactor - 10d;
                pic.Height = pic.Height * v_fFactor - 10d;
            }

            pic.Left = (double)m_objRange.Left + ((double)m_objRange.Width - pic.Width) / 2 + 1;
            pic.Top = (double)m_objRange.Top + ((double)m_objRange.Height - pic.Height) / 2 + 1;
            m_objRange = null;
        }

        public void InsertPictureBmp(int p_startRowIndex, string p_startColChars, int p_endRowIndex, string p_endColChars, string PicturePath)
        {

            Worksheet m_objWorkSheet = (Worksheet)base.Workbooks.ActiveSheet;
            Range m_objRange = GetRange(p_startRowIndex, p_startColChars, p_endRowIndex, p_endColChars);

            m_objRange.Select();
            Pictures pics = (Pictures)m_objWorkSheet.Pictures(oMissing);
            Picture pic = pics.Insert(PicturePath, oMissing);
            //pic.Left = (double)m_objRange.Left;
            //pic.Top = (double)m_objRange.Top;
            //if ((double)m_objRange.Height > (double)m_objRange.Width)
            //{
            //    pic.Height = (double)m_objRange.Width -2;
            //    pic.Width = (double)m_objRange.Width -2;
            //}
            //else
            //{
            //    pic.Width = (double)m_objRange.Height -2;
            //    pic.Height = (double)m_objRange.Height -2;
            //}

            ////m_objRange = null;
            double v_fFactor = 1;
            ////设置图片大小
            if (pic.Width * (double)m_objRange.Height > pic.Height * (double)m_objRange.Width)
                v_fFactor = (double)m_objRange.Width / (double)pic.Width;
            else
                v_fFactor = (double)m_objRange.Height / (double)pic.Height;

            double version = Convert.ToDouble(this.Application.Version);
            double width = pic.Width;
            double height = pic.Height;
            //pic.Width = width * v_fFactor - 4d;
            //pic.Height = height * v_fFactor - 4d;
            if (version >= 12.0)
            {
                pic.Width = width * v_fFactor - 4d;
                //pic.ShapeRange.Apply();
                pic.Height = height * v_fFactor - 4d;
                pic.ShapeRange.Apply();
            }
            else
            {
                pic.Width = width * v_fFactor - 4d;
                pic.Height = height * v_fFactor - 4d;
            }

            pic.Left = (double)m_objRange.Left + ((double)m_objRange.Width - pic.Width) / 2 + 1;
            pic.Top = (double)m_objRange.Top + ((double)m_objRange.Height - pic.Height) / 2 + 1;

            m_objRange = null;
        }

        #endregion

        #region SetColor(...)，参数对应于Range(...)，可以一个单元格也可以区域内的单元格一起设置同样的颜色。用Range或它的指定范围作为参数
        public void SetColor(int p_rowIndex, int p_colIndex, int ColorIndex)
        {
            //			xlApp.Cells[p_rowIndex,p_colIndex] = p_text;			
            Range range = GetRange(p_rowIndex, p_colIndex);
            range.Cells.Interior.ColorIndex = ColorIndex;
            range = null;
        }

        public void SetColor(int p_rowIndex, string p_colChars, int ColorIndex)
        {
            Range range = GetRange(p_rowIndex, p_colChars);
            range.Cells.Interior.ColorIndex = ColorIndex;
            range = null;
        }

        public void SetColor(int p_startRowIndex, int p_startColIndex, int p_endRowIndex, int p_endColIndex, int ColorIndex)
        {
            Range range = GetRange(p_startRowIndex, p_startColIndex, p_endRowIndex, p_endColIndex);
            range.Cells.Interior.ColorIndex = ColorIndex;
            range = null;
        }

        public void SetColor(int p_startRowIndex, string p_startColChars, int p_endRowIndex, string p_endColChars, int ColorIndex)
        {
            Range range = GetRange(p_startRowIndex, p_startColChars, p_endRowIndex, p_endColChars);
            range.Cells.Interior.ColorIndex = ColorIndex;
            range = null;
        }
        #endregion

        #region SetAlignment(...)，参数对应于Range(...)，可以一个单元格也可以区域内的单元格一起设置同样的对齐。用Range或它的指定范围作为参数
        public void SetAlignment(int p_rowIndex, int p_colIndex, RangHAlignment xlHAlignment, RangVAlignment xlVAlignment)
        {
            //			xlApp.Cells[p_rowIndex,p_colIndex] = p_text;			
            Range range = GetRange(p_rowIndex, p_colIndex);
            SetAlignment(range, xlHAlignment, xlVAlignment);
            range = null;


        }

        public void SetAlignment(int p_rowIndex, string p_colChars, RangHAlignment xlHAlignment, RangVAlignment xlVAlignment)
        {
            Range range = GetRange(p_rowIndex, p_colChars);
            SetAlignment(range, xlHAlignment, xlVAlignment);
            range = null;
        }

        public void SetAlignment(int p_startRowIndex, int p_startColIndex, int p_endRowIndex, int p_endColIndex, RangHAlignment xlHAlignment, RangVAlignment xlVAlignment)
        {
            Range range = GetRange(p_startRowIndex, p_startColIndex, p_endRowIndex, p_endColIndex);
            SetAlignment(range, xlHAlignment, xlVAlignment);
            range = null;
        }

        public void SetAlignment(int p_startRowIndex, string p_startColChars, int p_endRowIndex, string p_endColChars, RangHAlignment xlHAlignment, RangVAlignment xlVAlignment)
        {
            Range range = GetRange(p_startRowIndex, p_startColChars, p_endRowIndex, p_endColChars);
            SetAlignment(range, xlHAlignment, xlVAlignment);
            range = null;
        }
        #endregion

        #region 取对齐方式
        public void SetAlignment(Range p_Range, RangHAlignment xlHAlignment, RangVAlignment xlVAlignment)
        {
            p_Range.Select();
            p_Range.HorizontalAlignment = P_GetXlHAlign(xlHAlignment);
            p_Range.VerticalAlignment = P_GetXlHAlign(xlVAlignment);
        }
        private XlHAlign P_GetXlHAlign(RangHAlignment xlHAlignment)
        {
            XlHAlign xlH;
            switch (xlHAlignment)
            {
                case RangHAlignment.xlHAlignCenter:
                    xlH = XlHAlign.xlHAlignCenter;
                    break;
                case RangHAlignment.xlHAlignCenterAcrossSelection:
                    xlH = XlHAlign.xlHAlignCenterAcrossSelection;
                    break;
                case RangHAlignment.xlHAlignDistributed:
                    xlH = XlHAlign.xlHAlignDistributed;
                    break;
                case RangHAlignment.xlHAlignFill:
                    xlH = XlHAlign.xlHAlignFill;
                    break;
                case RangHAlignment.xlHAlignGeneral:
                    xlH = XlHAlign.xlHAlignGeneral;
                    break;
                case RangHAlignment.xlHAlignJustify:
                    xlH = XlHAlign.xlHAlignJustify;
                    break;
                case RangHAlignment.xlHAlignLeft:
                    xlH = XlHAlign.xlHAlignLeft;
                    break;
                case RangHAlignment.xlHAlignRight:
                    xlH = XlHAlign.xlHAlignRight;
                    break;
                default:
                    xlH = XlHAlign.xlHAlignLeft;
                    break;
            }
            return xlH;
        }

        private XlVAlign P_GetXlHAlign(RangVAlignment xlVAlignment)
        {
            XlVAlign xlV;
            switch (xlVAlignment)
            {
                case RangVAlignment.xlVAlignBottom:
                    xlV = XlVAlign.xlVAlignBottom;
                    break;
                case RangVAlignment.xlVAlignCenter:
                    xlV = XlVAlign.xlVAlignCenter;
                    break;
                case RangVAlignment.xlVAlignDistributed:
                    xlV = XlVAlign.xlVAlignDistributed;
                    break;
                case RangVAlignment.xlVAlignJustify:
                    xlV = XlVAlign.xlVAlignJustify;
                    break;
                case RangVAlignment.xlVAlignTop:
                    xlV = XlVAlign.xlVAlignTop;
                    break;
                default:
                    xlV = XlVAlign.xlVAlignTop;
                    break;
            }
            return xlV;
        }
        #endregion
    }//End class
}//End Namespace
namespace FBWMS
{
    class G_CommonClassExcel
    {
        public string mFilename;
        public Application m_objApp;
        public Workbooks m_objWorkbooks;
        public Workbook m_objWorkBook;
        public Worksheets m_objWorkSheets;
        public Worksheet m_objWorkSheet;
        private Range m_objRange = null;
        private readonly object m_objOpt = Missing.Value;

        public G_CommonClassExcel()
        {
            // 
            //   TODO:   在此处添加构造函数逻辑 
            // 
        }
        public void Create()//创建一个Excel对象 
        {
            m_objApp = new Application();
            m_objWorkbooks = m_objApp.Workbooks;
            m_objWorkBook = m_objWorkbooks.Add(true);
        }
        public void Open(string FileName)//打开一个Excel文件 
        {
            m_objApp = new Application();
            m_objWorkbooks = m_objApp.Workbooks;
            m_objWorkBook = m_objWorkbooks.Add(FileName);
            //wb   =   wbs.Open(FileName,     0,   true,   5, " ",   " ",   true,   XlPlatform.xlWindows,   "\t ",   false,   false,   0,   true,Type.Missing,Type.Missing); 
            //wb   =   wbs.Open(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing); 
            mFilename = FileName;
        }
        public Worksheet GetSheet(string SheetName)//获取一个工作表 
        {
            Worksheet s = (Worksheet)m_objWorkBook.Worksheets[SheetName];
            return s;
        }
        public Worksheet AddSheet(string SheetName)//添加一个工作表 
        {
            Worksheet s = (Worksheet)m_objWorkBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            s.Name = SheetName;
            return s;
        }

        public void DelSheet(string SheetName)//删除一个工作表 
        {
            ((Worksheet)m_objWorkBook.Worksheets[SheetName]).Delete();
        }
        public Worksheet ReNameSheet(string OldSheetName, string NewSheetName)//重命名一个工作表一 
        {
            Worksheet s = (Worksheet)m_objWorkBook.Worksheets[OldSheetName];
            s.Name = NewSheetName;
            return s;
        }

        public Worksheet ReNameSheet(Worksheet Sheet, string NewSheetName)//重命名一个工作表二 
        {

            Sheet.Name = NewSheetName;

            return Sheet;
        }

        public void SetCellValue(Worksheet ws, int x, int y, object value)//ws：要设值的工作表           X行Y列           value       值   
        {
            ws.Cells[x, y] = value;
        }
        public void SetCellValue(string ws, int x, int y, object value)//ws：要设值的工作表的名称   X行Y列   value   值 
        {

            GetSheet(ws).Cells[x, y] = value;
        }

        public void SetCellProperty(Worksheet ws, int Startx, int Starty, int Endx, int Endy, int size, string name, Constants color, Constants HorizontalAlignment)//设置一个单元格的属性       字体，       大小，颜色       ，对齐方式 
        {
            //name = "宋体 ";
            //size = 12;
            //color = Constants.xlAutomatic;
            //HorizontalAlignment = Constants.xlRight;
            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Name = name;
            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Size = size;
            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Color = color;
            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).HorizontalAlignment = HorizontalAlignment;
        }

        public void SetCellProperty(string wsn, int Startx, int Starty, int Endx, int Endy, int size, string name, Constants color, Constants HorizontalAlignment)
        {
            //name   =   "宋体 "; 
            //size   =   12; 
            //color   =   Constants.xlAutomatic; 
            //HorizontalAlignment   =   Constants.xlRight; 

            Worksheet ws = GetSheet(wsn);
            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Name = name;
            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Size = size;
            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Color = color;

            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).HorizontalAlignment = HorizontalAlignment;
        }


        public void UniteCells(Worksheet ws, int x1, int y1, int x2, int y2)//合并单元格 
        {
            ws.get_Range(ws.Cells[x1, y1], ws.Cells[x2, y2]).Merge(Type.Missing);
        }

        public void UniteCells(string ws, int x1, int y1, int x2, int y2)//合并单元格 
        {
            GetSheet(ws).get_Range(GetSheet(ws).Cells[x1, y1], GetSheet(ws).Cells[x2, y2]).Merge(Type.Missing);

        }


        public void InsertTable(DataTable dt, string ws, int startX, int startY)//将内存中数据表格插入到Excel指定工作表的指定位置   为在使用模板时控制格式时使用一 
        {

            for (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {
                    GetSheet(ws).Cells[startX + i, j + startY] = dt.Rows[i][j].ToString();

                }

            }

        }
        public void InsertTable(DataTable dt, Worksheet ws, int startX, int startY)//将内存中数据表格插入到Excel指定工作表的指定位置二 
        {

            for (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {

                    ws.Cells[startX + i, j + startY] = dt.Rows[i][j];

                }

            }

        }


        public void AddTable(DataTable dt, string ws, int startX, int startY)//将内存中数据表格添加到Excel指定工作表的指定位置一 
        {

            for (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {

                    GetSheet(ws).Cells[i + startX, j + startY] = dt.Rows[i][j];

                }

            }

        }
        public void AddTable(DataTable dt, Worksheet ws, int startX, int startY)//将内存中数据表格添加到Excel指定工作表的指定位置二 
        {


            for (int j = 0; j <= dt.Columns.Count - 1; j++)
            {
                switch (dt.Columns[j].DataType.ToString())
                {
                    case "System.String":
                        ws.get_Range(ws.Cells[1, j + 1], ws.Cells[dt.Rows.Count + 1, j + 1]).NumberFormatLocal = "@";
                        break;
                    case "System.DateTime":
                        ws.get_Range(ws.Cells[1, j + 1], ws.Cells[dt.Rows.Count + 1, j + 1]).NumberFormatLocal = "yyyy-mm-dd";
                        break;
                }
                for (int i = 0; i <= dt.Rows.Count - 1; i++)
                {

                    ws.Cells[i + startX, j + startY] = dt.Rows[i][j];

                }
            }

        }
        public void InsertPicture(string RangeName, string PicturePath)//插入图片操作一 
        {
            m_objRange = m_objWorkSheet.get_Range(RangeName, m_objOpt);
            m_objRange.Select();
            Pictures pics = (Pictures)m_objWorkSheet.Pictures(m_objOpt);
            pics.Insert(PicturePath, m_objOpt);
            double v_fFactor = 1;
            //设置图片大小
            if (pics.Width * (double)m_objRange.Height > pics.Height * (double)m_objRange.Width)
                v_fFactor = (double)m_objRange.Width / (double)pics.Width;
            else
                v_fFactor = (double)m_objRange.Height / (double)pics.Height;
            pics.Left = (double)m_objRange.Left + ((double)m_objRange.Width - (pics.Width * v_fFactor)) / 2 + 1;
            pics.Top = (double)m_objRange.Top + ((double)m_objRange.Height - (pics.Height * v_fFactor)) / 2 + 1;
            pics.Width = pics.Width * v_fFactor - 0.5d;
            pics.Height = pics.Height * v_fFactor - 0.5d;
            //GetSheet(ws).Shapes.AddPicture(Filename,Microsoft.Office.Core.MsoTriState.msoFalse, MsoTriState.msoTrue, 10, 10, 150, 150);//后面的数字表示位置 
            //GetSheet(ws).Shapes.
        }

        //public   void   InsertPictures(string   Filename,   string   ws,   int   Height,   int   Width)//插入图片操作二 
        //{ 
        //         GetSheet(ws).Shapes.AddPicture(Filename,   MsoTriState.msoFalse,   MsoTriState.msoTrue,   10,   10,   150,   150); 
        //         GetSheet(ws).Shapes.get_Range(Type.Missing).Height   =   Height; 
        //         GetSheet(ws).Shapes.get_Range(Type.Missing).Width   =   Width; 
        //} 
        //public   void   InsertPictures(string   Filename,   string   ws,   int   left,   int   top,   int   Height,   int   Width)//插入图片操作三 
        //{ 

        //         GetSheet(ws).Shapes.AddPicture(Filename,   MsoTriState.msoFalse,   MsoTriState.msoTrue,   10,   10,   150,   150); 
        //         GetSheet(ws).Shapes.get_Range(Type.Missing).IncrementLeft(left); 
        //         GetSheet(ws).Shapes.get_Range(Type.Missing).IncrementTop(top); 
        //         GetSheet(ws).Shapes.get_Range(Type.Missing).Height   =   Height; 
        //         GetSheet(ws).Shapes.get_Range(Type.Missing).Width   =   Width; 
        //} 

        public void InsertActiveChart(XlChartType ChartType, string ws, int DataSourcesX1, int DataSourcesY1, int DataSourcesX2, int DataSourcesY2, XlRowCol ChartDataType)//插入图表操作 
        {
            ChartDataType = XlRowCol.xlColumns;
            m_objWorkBook.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            {
                m_objWorkBook.ActiveChart.ChartType = ChartType;
                m_objWorkBook.ActiveChart.SetSourceData(GetSheet(ws).get_Range(GetSheet(ws).Cells[DataSourcesX1, DataSourcesY1], GetSheet(ws).Cells[DataSourcesX2, DataSourcesY2]), ChartDataType);
                m_objWorkBook.ActiveChart.Location(XlChartLocation.xlLocationAsObject, ws);
            }
        }
        public bool Save()//保存文档 
        {
            if (mFilename == " ")
            {
                return false;
            }
            else
            {
                try
                {
                    m_objWorkBook.Save();
                    return true;
                }

                catch (Exception ex)
                {
                    return false;
                }
            }
        }
        public bool SaveAs(object FileName)//文档另存为 
        {
            try
            {
                m_objWorkBook.SaveAs(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                return true;

            }

            catch (Exception ex)
            {
                return false;

            }
        }
        public void Close()//关闭一个Excel对象，销毁对象 
        {
            //wb.Save(); 
            m_objWorkBook.Close(Type.Missing, Type.Missing, Type.Missing);
            m_objWorkbooks.Close();
            m_objApp.Quit();
            m_objWorkBook = null;
            m_objWorkbooks = null;
            m_objApp = null;
            GC.Collect();
        }

        public static void Dst2Excel(ref DataSet _dst)
        {
            try
            {
                Application xlApp = new Application();
                if (xlApp == null)
                {
                    MessageBox.Show(Resources.Object);
                    return;
                }
                if (_dst == null || _dst.Tables.Count <= 0)
                {
                    MessageBox.Show(Resources.Dst2Excel);
                    return;
                }
                xlApp.Visible = true;

                object missing = Missing.Value;
                int countcolumns = _dst.Tables[0].Columns.Count;
                int countrows = _dst.Tables[0].Rows.Count;
                string A = Get_TableHeader(countcolumns);
                object[,] myvalues1 = new object[countrows + 1, countcolumns];

                Workbooks workbooks = xlApp.Workbooks;
                Workbook theWorkbook = workbooks.Add(missing);
                Sheets sheets = theWorkbook.Worksheets;
                Worksheet worksheet = (Worksheet)sheets.get_Item(1);
                Range range = worksheet.get_Range("A1", A + (countrows + 1).ToString());

                // 标题行
                for (int j = 0; j < countcolumns; j++)
                {
                    myvalues1[0, j] = _dst.Tables[0].Columns[j].Caption;
                }

                // 内容行
                for (int j = 0; j < countcolumns; j++)
                {
                    switch (_dst.Tables[0].Columns[j].DataType.ToString())
                    {
                        case "System.String":
                            worksheet.get_Range(worksheet.Cells[1, j + 1], worksheet.Cells[countrows + 1, j + 1]).NumberFormatLocal = "@";
                            break;
                        case "System.DateTime":
                            worksheet.get_Range(worksheet.Cells[1, j + 1], worksheet.Cells[countrows + 1, j + 1]).NumberFormatLocal = "yyyy-mm-dd";
                            break;
                    }

                    for (int i = 1; i < countrows + 1; i++)
                    {
                        myvalues1[i, j] = _dst.Tables[0].Rows[i - 1][j];
                    }
                }

                range.Cells.Value2 = myvalues1;
                workbooks = null;
                theWorkbook = null;
                sheets = null;
                worksheet = null;
                range = null;
                xlApp = null;
            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }

        public static string Get_TableHeader(int num)
        {

            string A = "";
            for (int i = 0; i < num; i++)
            {
                if ((65 + i) < 91)
                    A = ((char)(65 + i)).ToString();
                else

                    A = "A" + ((char)(i + 39)).ToString();   //i+39:Z后的字符转成A、B

            }
            return A;
        }
    }

}
